Published: 2007-01-23
Applies to:
- Content Studio ver. 4.x
Type: Information
Symptoms
After moving the Content Studio database the full text search does not work correctly.
Word or phrases that earlier was found are no longer found.
Cause
Microsoft has completely rebuilt the full-text indexing engine for the SQL Server
2005 product. In earlier version the full-text indexing relied on a operation system
indexing engine but SQL Server 2005 has its own engine. Content Studio under SQL
Server 2000 used the language neutral word breaking algorithm which does not work
as expected in SQL Server 2005. All content in Content Studio is stored in a binary
blob field that gets full-text indexed. In order to determine the nature of the
content in this binary field another field that stores the file extension (ex. .PDF
or .DOC) for each saved document to provide information to the indexing engine how
to interpret the binary content when indexing it. In SQL Server 2005 the language
neutral word breaker no longer works with this extra field and the indexing engine
cannot determine the nature of the content.
Resolution
Rebuild the index using the SQL script provided as a script. This script has been around for a while but has now been rebuilt to support SQL Server 2005.
Download the script that now supports both SQL Server 2000 and 2005.
For SQL Server 2005 the script specifies the Swedish word breaker by default which uses another noise word list that ex. the English word breaker. You can replace the occurrence of the word Swedish with your language before executing the script.
-- Drop all CS fulltext index catalogs on the current database
-- if they have the wrong name
DECLARE @Ver int;
IF(CHARINDEX ('9.00.', @@VERSION) > 0)
SELECT @Ver = 2005;
ELSE
SELECT @Ver = 2000;
DECLARE @script nvarchar(4000);
--Drop all fulltext indexes if they exists
IF COLUMNPROPERTY(OBJECT_ID(N'dbo.tbl_modules_content'),N'ContentBinary',N'IsFulltextIndexed') = 1
BEGIN
IF @Ver = 2005
EXECUTE sp_executesql N'DROP FULLTEXT INDEX ON dbo.tbl_modules_content;';
ELSE
EXECUTE sp_fulltext_table N'dbo.tbl_modules_content', N'drop';
END
IF COLUMNPROPERTY(OBJECT_ID(N'dbo.tbl_Xml_data'),N'FullData',N'IsFulltextIndexed') = 1
BEGIN
IF @Ver = 2005
EXECUTE sp_executesql N'DROP FULLTEXT INDEX ON dbo.tbl_Xml_data;';
ELSE
EXECUTE sp_fulltext_table N'dbo.tbl_Xml_data', N'drop';
END
DECLARE @catalog sysname;
DECLARE Curs CURSOR FAST_FORWARD READ_ONLY FOR
SELECT [name] FROM dbo.sysfulltextcatalogs
OPEN Curs;
FETCH NEXT FROM Curs INTO @catalog
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Ver = 2005
SELECT @script = N'DROP FULLTEXT CATALOG [' + @Catalog + ']'
ELSE
SELECT @script = N'EXECUTE sp_fulltext_catalog [' + @Catalog + '], N''drop'';';
EXECUTE sp_executesql @script;
FETCH NEXT FROM Curs INTO @catalog
END
CLOSE Curs;
DEALLOCATE Curs;
GO
--The content fulltext column
DECLARE @Ver int;
IF(CHARINDEX ('9.00.', @@VERSION) > 0)
SELECT @Ver = 2005;
ELSE
SELECT @Ver = 2000;
DECLARE @Catalog sysname;
DECLARE @script nvarchar(3000);
SELECT @Catalog = 'ftxt_' + CAST(DB_ID() AS nvarchar(10)) + N'_CS_ContentBinary';
IF NOT EXISTS (SELECT * FROM dbo.sysfulltextcatalogs WHERE [name] = @Catalog)
BEGIN
IF @Ver = 2005
SELECT @script = N'CREATE FULLTEXT CATALOG [' + @Catalog + ']'
ELSE
SELECT @script = N'EXECUTE sp_fulltext_catalog [' + @Catalog + '], N''create'';';
EXECUTE sp_executesql @script;
END
--drop the index if existing
IF COLUMNPROPERTY(OBJECT_ID(N'dbo.tbl_modules_content'),N'ContentBinary',N'IsFulltextIndexed') = 1
BEGIN
IF @Ver = 2005
EXECUTE sp_executesql N'DROP FULLTEXT INDEX ON dbo.tbl_modules_content;';
ELSE
EXECUTE sp_fulltext_table N'dbo.tbl_modules_content', N'drop';
END
GO
DECLARE @Ver int;
IF(CHARINDEX ('9.00.', @@VERSION) > 0)
SELECT @Ver = 2005;
ELSE
SELECT @Ver = 2000;
DECLARE @Catalog sysname;
DECLARE @script nvarchar(3000);
SELECT @Catalog = 'ftxt_' + CAST(DB_ID() AS nvarchar(10)) + N'_CS_ContentBinary';
IF @Ver = 2005
BEGIN
SELECT @script = N'
CREATE FULLTEXT INDEX ON [dbo].[tbl_modules_content]
(
[ContentBinary] TYPE COLUMN [Fil_Ext] LANGUAGE ''Swedish'',
[introduction] LANGUAGE ''Swedish'',
[Keywords] LANGUAGE ''Swedish''
)
KEY INDEX [PK_tbl_modules_content] ON [' + @Catalog + '] WITH CHANGE_TRACKING MANUAL;';
EXECUTE sp_executesql @script;
SELECT @script = N'ALTER FULLTEXT INDEX ON [dbo].[tbl_modules_content] ENABLE;';
EXECUTE sp_executesql @script;
END
ELSE
BEGIN
EXECUTE sp_fulltext_table N'[dbo].[tbl_modules_content]', N'create', @Catalog, N'PK_tbl_modules_content'
EXECUTE sp_fulltext_column N'[dbo].[tbl_modules_content]', N'introduction', N'add', 0
EXECUTE sp_fulltext_column N'[dbo].[tbl_modules_content]', N'Keywords', N'add', 0
EXECUTE sp_fulltext_column N'[dbo].[tbl_modules_content]', N'ContentBinary', N'add', 0, N'Fil_ext'
EXECUTE sp_fulltext_table N'[dbo].[tbl_modules_content]', N'activate';
END
GO
--The Xml-index fulltext column
DECLARE @Ver int;
IF(CHARINDEX ('9.00.', @@VERSION) > 0)
SELECT @Ver = 2005;
ELSE
SELECT @Ver = 2000;
DECLARE @Catalog sysname;
DECLARE @script nvarchar(3000);
SELECT @Catalog = N'ftxt_' + CAST(DB_ID(DB_NAME()) AS nvarchar(10)) + N'_XML_Data'
IF NOT EXISTS (SELECT * FROM dbo.sysfulltextcatalogs WHERE [name] = @Catalog)
BEGIN
IF @Ver = 2005
SELECT @script = N'CREATE FULLTEXT CATALOG [' + @Catalog + ']' /*+
N'IN PATH ''E:\SQL Server 2005\FTDATA'';';*/
ELSE
SELECT @script = N'EXECUTE sp_fulltext_catalog [' + @Catalog + '], N''create'';';
EXECUTE sp_executesql @script;
END
--drop the index if existing
IF COLUMNPROPERTY(OBJECT_ID(N'dbo.tbl_Xml_data'),N'FullData',N'IsFulltextIndexed') = 1
BEGIN
IF @Ver = 2005
EXECUTE sp_executesql N'DROP FULLTEXT INDEX ON dbo.tbl_Xml_data;';
ELSE
EXECUTE sp_fulltext_table N'dbo.tbl_Xml_data', N'drop';
END
GO
DECLARE @Ver int;
IF(CHARINDEX ('9.00.', @@VERSION) > 0)
SELECT @Ver = 2005;
ELSE
SELECT @Ver = 2000;
DECLARE @Catalog sysname;
DECLARE @script nvarchar(3000);
SELECT @Catalog = 'ftxt_' + CAST(DB_ID() AS nvarchar(10)) + N'_XML_Data';
IF @Ver = 2005
BEGIN
SELECT @script = N'
CREATE FULLTEXT INDEX ON [dbo].[tbl_xml_data]
(
[FullData] LANGUAGE ''Swedish''
)
KEY INDEX [PK_tbl_xml_data] ON [' + @Catalog + '] WITH CHANGE_TRACKING AUTO;';
EXECUTE sp_executesql @script;
SELECT @script = N'ALTER FULLTEXT INDEX ON [dbo].[tbl_xml_data] ENABLE;';
EXECUTE sp_executesql @script;
END
ELSE
BEGIN
EXECUTE sp_fulltext_table N'[dbo].[tbl_xml_data]', N'create', @Catalog, N'PK_tbl_xml_data';
EXECUTE sp_fulltext_column N'[dbo].[tbl_xml_data]', N'FullData', N'add', 0;
EXECUTE sp_fulltext_table N'[dbo].[tbl_xml_data]', N'activate';
--start background index update
EXECUTE sp_fulltext_table N'[dbo].[tbl_xml_data]', N'Start_change_tracking';
EXECUTE sp_fulltext_table N'[dbo].[tbl_xml_data]', N'Start_background_updateindex';
END
How to execute this script.
- Start Enterprise Manager (SQL Server 2000) or SQL Server Management Studio (SQL Server 2005).
- Connect to the server instance that has the Content Studio databases.
- Create a new query window and paste in the script just downloaded.
- Select a Content Studio site database to rebuild the index for.
- Execute the script by hitting the F5 key. During the execution you might get a warning message that indicates that the full-text index does not support background indexing when using the WRITETEXT T-SQL command. You can safely ignore this warning.
- Repeat the step 4 - 5 until you have rebuilt all the Content Studio site databases you have problems with.